package com.implementsfaces.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import org.springframework.util.ClassUtils;

import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.engine.execution.SqlExecutor;
import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;
import com.interfaces.dao.GenericDao;
import com.interfaces.dao.IBatisDaoUtils;
import com.pub.LimitSqlExecutor;
import com.pub.ReflectUtil;
import com.util.PaginatedArrayList;
import com.util.PaginatedList;
import com.util.Utils;

/**
 * 
 * <p>
 * 功能描述:减nterface和implement类，ibatis
 * xml的id写法必须严格按照标准写法 主要是通过反射获得bean的类名再组装成ibatis中xml的id名 example:
 * 获得多条记录：IBatisDaoUtils.getSelectQuery(getPackage()
						+ ClassUtils.getShortName(User.class) 结果为：
 * user.getUsers ,多条记录,那么再ibatis的xml中就必须定义一个getUsers的id
 * ClassUtils.getShortName(Class) : 获得类的类名
 * </p>
 * 
 * @author <a href="mailto: zhuyoulong@gtss.com.cn">zhuyoulong</a>
 * @create_date 2008-10-27
 * @version $ Revision: 1.0
 */
@SuppressWarnings({ "serial", "unchecked" })
public class GenericDaoiBatis extends SqlMapClientDaoSupport implements
		GenericDao {
	private Logger logger = Logger.getLogger(GenericDaoiBatis.class);
	
	private JdbcTemplate jdbcTemplate;
	public GenericDaoiBatis() {
		 		  
	}

	public GenericDaoiBatis(Class persistentClass) {
		this.persistentClass = persistentClass;
	}
	public void setClass(Class persistentClass){
		this.persistentClass = persistentClass;
	}

	/**
	 * 
	 * <p>功能描述：ibatis设置了sql语句需要命名空间支持<br>
	 * 即：useStatementNamespaces="true" <br>
	 * 所以dao需要添加包名来调用
	 * </p>
	 * @return
	 * @author:zhuyoulong
	 * @update:[日期YYYY-MM-DD][更改人姓名][变更描述]
	 */
	public String getPackage(){
//		return ClassUtils.getShortName(persistentClass).toLowerCase()+".";
		return Utils.replaceFirstWord(ClassUtils.getShortName(persistentClass))+".";
	}
	
	
	/**
	 * 获得所有对象记录
	 */
	public List getAll() {
		return getSqlMapClientTemplate().queryForList(
				getPackage()
						+ IBatisDaoUtils.getSelectQuery(ClassUtils
								.getShortName(persistentClass)), null);
	}
	
	
	public List getAll(Object obj) {
		return getSqlMapClientTemplate().queryForList(
				getPackage()
						+ IBatisDaoUtils.getSelectQuery(ClassUtils
								.getShortName(persistentClass)), obj);
	}

	/**
	 * 通过id获得对象
	 */
	public Object get(Serializable id) {
		Object object = getSqlMapClientTemplate().queryForObject(
				IBatisDaoUtils.getFindQuery(getPackage()
						+ ClassUtils
						.getShortName(persistentClass)), id);
		if (object == null) {
			log.warn((new StringBuilder()).append("Uh oh, '").append(
					persistentClass).append("' object with id '").append(id)
					.append("' not found...").toString());
			throw new ObjectRetrievalFailureException(getPackage()
					+ ClassUtils
					.getShortName(persistentClass), id);
		} else {
			return object;
		}
	}

	/**
	 * 通过id判断记录是否存在
	 */
	public boolean exists(Serializable id) {
		Object object = getSqlMapClientTemplate().queryForObject(
				IBatisDaoUtils.getFindQuery(getPackage()
						+ ClassUtils
						.getShortName(persistentClass)), id);
		return object != null;
	}

	/**
	 * 插入和修改记录方法 ibatis xml写法<br>
	 * 如：insertUser
	 * 如：updateUser 
	 */
	public Object save(Object object) {
		String className = ClassUtils.getShortName(object.getClass());
		Object primaryKey = IBatisDaoUtils.getPrimaryKeyValue(object);
		Class primaryKeyClass = IBatisDaoUtils.getPrimaryKeyFieldType(object);
		String keyId = null;
		if (primaryKey != null)
			keyId = primaryKey.toString();
		//如果id为空就执行插入
		if (StringUtils.isBlank(keyId)) {
			IBatisDaoUtils.prepareObjectForSaveOrUpdate(object);
			primaryKey = getSqlMapClientTemplate().insert(getPackage()+
					IBatisDaoUtils.getInsertQuery(className), object);
			IBatisDaoUtils.setPrimaryKey(object, primaryKeyClass, primaryKey);
		} else {//更新
			IBatisDaoUtils.prepareObjectForSaveOrUpdate(object);
			getSqlMapClientTemplate().update(getPackage()+
					IBatisDaoUtils.getUpdateQuery(className), object);
		}
		if (IBatisDaoUtils.getPrimaryKeyValue(object) == null)
			throw new ObjectRetrievalFailureException(className, object);
		else
			return object;
	}

	/**
	 * 通过id删除记录<br>
	 * 如：deleteUser
	 */
	public void remove(Serializable id) {
		getSqlMapClientTemplate().update(
				IBatisDaoUtils.getDeleteQuery(getPackage()
						+ ClassUtils
						.getShortName(persistentClass)), id);
	}
	
	public List<Map<String,String>> queryString(String sql) {
		return (List<Map<String,String>>)getSqlMapClientTemplate().queryForList("common.queryString",sql);		
	}
	
	
	/**
	 * 直接用SQL来执行查询语句 
	 */
	public List<Map<String, String>> executeQuery(String sql) {
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		List<Map<String,String>> rows =null;
		try {
			conn = getSqlMapClientTemplate().getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			logger.debug("查询多条数据 sql = "+sql);
			rs =pstmt.executeQuery();
			rows= new ArrayList<Map<String,String>>();
			ResultSetMetaData rsmd=rs.getMetaData();
			int columnCount=rsmd.getColumnCount();
			String []columns=new String[columnCount];
			for(int i=1;i<=columnCount;i++){
				columns[i-1]=rsmd.getColumnLabel(i);
			}
			rsmd=null;
			Map<String,String> row=null;
			while (rs.next()){
				row=new HashMap<String,String>(columnCount);
				for (int i=1;i<=columnCount;i++) {
					row.put(columns[i-1],rs.getString(i));
				}
				rows.add(row);				
			}
			row=null;
			columns=null;
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		finally{
			try {
				if(rs!=null){
					rs.close();
					rs=null;
				}
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}
				if(conn!=null){
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return rows;
	}
	
	
	
	/**
	 * 直接用JDBC的SQL来执行更新与删除语句
	 */
	public void executeUpdate(String sql) {
		Connection conn = null;
		Statement pstmt = null;
//		SqlMapClient sqlmapClient = getSqlMapClientTemplate().getSqlMapClient();
		try {
			conn = getSqlMapClientTemplate().getDataSource().getConnection();
//			sqlmapClient.startTransaction();
			conn.setAutoCommit(false);
			pstmt = conn.createStatement();
			pstmt.setEscapeProcessing(false);
			pstmt.execute(sql);
//			sqlmapClient.commitTransaction();
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
//				sqlmapClient.endTransaction();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally{
			try {
//				if(sqlmapClient!=null){
//					sqlmapClient=null;
//				}
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}
				if(conn!=null)
				{
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}


	/**
	 * 返回一条记录信息,在写程序时确保只有一条记录
	 */
	public Map<String, String> executeMap(String sql) {
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs=null;
		Map<String,String> row =null;
		try {
			conn = getSqlMapClientTemplate().getDataSource().getConnection();
			pstmt = conn.prepareStatement(sql);
			logger.debug("查询单条数据 sql = "+sql);
			rs =pstmt.executeQuery();
			row= new HashMap<String,String>();
			ResultSetMetaData rsmd=rs.getMetaData();
			int columnCount=rsmd.getColumnCount();
			String []columns=new String[columnCount];
			for(int i=1;i<=columnCount;i++){
				columns[i-1]=rsmd.getColumnLabel(i);
			}
			rsmd=null;
			if (rs.next()){
				row=new HashMap<String,String>(columnCount);
				for (int i=1;i<=columnCount;i++) {
					row.put(columns[i-1],rs.getString(i));
				}
			}
			columns=null;
		} catch (Exception exception) {
			exception.printStackTrace();
		}
		finally{
			try {
				if(rs!=null){
					rs.close();
					rs=null;
				}
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}
				if(conn!=null){
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return row;
	}
	

	/**
	 * 直接用JDBC的SQL来执行更新与删除语句
	 */
	public void runQuery(String sql, boolean noUpdateCount) {
		Connection conn = null;
		Statement pstmt = null;
//		SqlMapClient sqlmapClient = getSqlMapClientTemplate().getSqlMapClient();
		try {
			conn = getSqlMapClientTemplate().getDataSource().getConnection();
//			sqlmapClient.startTransaction();
			conn.setAutoCommit(false);
			pstmt = conn.createStatement();
			pstmt.setEscapeProcessing(false);
			pstmt.execute(sql);
			logger.debug("更新或删除语句 sql = "+sql);
//			sqlmapClient.commitTransaction();
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
//				sqlmapClient.endTransaction();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally{
			try {
//				if(sqlmapClient!=null){
//					sqlmapClient=null;
//				}
				if(pstmt!=null){
					pstmt.close();
					pstmt=null;
				}
				if(conn!=null)
				{
					conn.close();
					conn=null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public PaginatedList<Map<String, String>> getPageListSql(String countSql,int pageNo, int pageSize) {
		PaginatedArrayList paginatedArrayList = new PaginatedArrayList(this.getCountSql(countSql), pageNo, pageSize);
		return paginatedArrayList;
	}
	/**
	 * <p>查询数量的别名必须是COUNT
	 * 比如：select count(*) as count from table </p>
	 * @param countSql
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @author:zhuyoulong
	 * @update:[日期YYYY-MM-DD][更改人姓名][变更描述]
	 */
	public PaginatedList<Map<String, String>> getPageListSql2(String countSql,int pageNo, int pageSize) {
		Map<String,String> countmap = this.executeMap(countSql);
		PaginatedArrayList paginatedArrayList = new PaginatedArrayList(Integer.parseInt(countmap.get("COUNT")), pageNo, pageSize);
		return paginatedArrayList;
	}
	/**
	 * SQL分页查询
	 */
	public PaginatedList<Map<String, String>> executeQueryPage(String sql, String countSql, int pageNo, int pageSize) {
		logger.debug("分页查询count countSql = "+countSql+" \n like example : select count(*) as count from table \n");
		PaginatedList paginatedArrayList = this.getPageListSql2(countSql,pageNo, pageSize);
		String sql_start = "select * from ( select a.*,rownum rowno from ( ";
		String sql_end = " ) a where rownum <= "+paginatedArrayList.getEndPos()+")  b where rowno >= "+paginatedArrayList.getStartPos()+"";
		sql = sql_start + sql + sql_end;
		logger.debug("分页查询 sql = "+sql);
        paginatedArrayList.addAll(this.executeQuery(sql));
        return paginatedArrayList;
	}

	public PaginatedList<Map<String,String>> queryStringPage(String sql, String countSql,int pageNo, int pageSize){
		PaginatedList paginatedArrayList = this.getPageListSql(countSql,pageNo, pageSize);
		HashMap map = new HashMap();
		map.put("startPos", paginatedArrayList.getStartPos());
		map.put("endPos", paginatedArrayList.getEndPos());
		map.put("sql", sql);
        paginatedArrayList.addAll(this.getListSql(map));
        return paginatedArrayList;
	}
	
	private Collection getListSql(HashMap map){
		return getSqlMapClientTemplate().queryForList("common.queryStringPage",map);
	}

	public int getCountSql(String sql) {
		return (Integer)getSqlMapClientTemplate().queryForObject("common.queryStringCount",sql);
	}
	public int getCountSql2(String sql) {
		return (Integer)getSqlMapClientTemplate().queryForObject("common.queryStringCount",sql);
	}
	/**
	 * 根据条件获得记录总数
	 */
	public int getCount(Object obj) {
		this.setClass(obj.getClass());
		return (Integer)getSqlMapClientTemplate().queryForObject(getPackage()+IBatisDaoUtils.getCount( ClassUtils.getShortName(persistentClass)),obj);
		
	}
	public int getCount(Object obj,String countid) {
		this.setClass(obj.getClass());
		return (Integer)getSqlMapClientTemplate().queryForObject(getPackage()+countid,obj);
		
	}
	/**
	 * 获得分页数组
	 * @param obj
	 * @param pageIndex
	 * @param pageSize
	 * @return
	 * @author:zhuyoulong
	 * @update:[日期YYYY-MM-DD][更改人姓名][变更描述]
	 */
	public PaginatedList<Object> getPageList(Object obj,int pageIndex,int pageSize){
		this.setClass(obj.getClass());
		PaginatedArrayList paginatedArrayList = new PaginatedArrayList(this.getCount(obj), pageIndex, pageSize);
		return paginatedArrayList;
	}
	
	public PaginatedList<Object> getPageList(Object obj,String countid,int pageIndex,int pageSize){
		this.setClass(obj.getClass());
		PaginatedArrayList paginatedArrayList = new PaginatedArrayList(this.getCount(obj,countid), pageIndex, pageSize);
		return paginatedArrayList;
	}
    
	/**
	 * 
	 * 根据条件获得记录总数
	 * @param ob
	 * @return
	 */
	public int getConut1(Object ob){
		this.setClass(ob.getClass());
		return (Integer)getSqlMapClientTemplate().queryForObject(getPackage()+ IBatisDaoUtils.getCount1(ClassUtils.getShortName(persistentClass)),ob);
	}
	
	public PaginatedList<Object> getPageList1(Object ob,int pageIndex,int pageSize){
		this.setClass(ob.getClass());
		PaginatedArrayList paginatedArrayList = new PaginatedArrayList(this.getConut1(ob),pageIndex,pageSize);
		return paginatedArrayList;
	}

	protected final Log log = LogFactory.getLog(getClass());

	private Class persistentClass;

	/**
	 * 测试方法
	 * @param args
	 * @author:zhuyoulong
	 * @update:[日期YYYY-MM-DD][更改人姓名][变更描述]
	 */
	public static void main(String[] args) {
//		GenericDaoiBatis genericDaoiBatis = new GenericDaoiBatis(
//				LoginBean.class);
		// String name = ClassUtils.getShortName(genericDaoiBatis);
		// System.out.println("name = "+name);
//		genericDaoiBatis.getObjects(LoginBean.class);
	}

	public void getObjects(Class clazz) {
		String className = ClassUtils.getShortName(clazz);
		System.out.println("name = " + className);
		String b = (new StringBuilder()).append("get").append(className)
				.append("s").toString();
		System.out.println("b = " + b);
	}

	private SqlExecutor sqlExecutor;

	public SqlExecutor getSqlExecutor() {
		return sqlExecutor;
	}

	public void setSqlExecutor(SqlExecutor sqlExecutor) {
		this.sqlExecutor = sqlExecutor;
	}

	public void setEnableLimit(boolean enableLimit) {
		if (sqlExecutor instanceof LimitSqlExecutor) {
			((LimitSqlExecutor) sqlExecutor).setEnableLimit(enableLimit);
		}
	}

	public void initialize() throws Exception {
		if (sqlExecutor != null) {
			SqlMapClient sqlMapClient = getSqlMapClientTemplate()
					.getSqlMapClient();
			if (sqlMapClient instanceof ExtendedSqlMapClient) {
				ReflectUtil.setFieldValue(((ExtendedSqlMapClient) sqlMapClient)
						.getDelegate(), "sqlExecutor", SqlExecutor.class,
						sqlExecutor);
			}
		}
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	
}
